#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time     : 2020/8/20 11:54
# @Author   : hejiang
# @File     : 中棉导入汇总.py
# @Software : vsCode

import pymysql
import time
import datetime
import uuid
import os
import xlrd


class mysql_class:
    def __init__(self):
        '''初始化实例属性'''
        self.host = '192.168.101.27'
        self.user = 'root'
        self.passwd = '123456'
        self.db = 'zhongmian_qc'
        self.file_name = '业务单元列表打印模板.xls'
        self.file_name2 = '业务单元列表打印模板.xls'
        self.db_table = 'v_tbl_func_org'
        self.TENANT_CODE = 'T0025'

    def ljdb(self):
        '''DB连接'''
        self.conn = pymysql.connect(host=self.host,
                                    user=self.user,
                                    passwd=self.passwd,
                                    db=self.db,
                                    port=3306,
                                    charset='utf8')
        # conn = pymysql.connect(ip, username, pwd, schema,port)
        self.conn.ping(True)  #使用mysql ping来检查连接,实现超时自动重新连接
        # print(getCurrentTime(), u"MySQL DB Connect Success:",user+'@'+host+':'+str(port)+'/'+db)
        self.cur = self.conn.cursor()

    # 获取当前时间
    def getCurrentTime(self):
        return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

    # 删除数据
    def deleteData(self):
        pass
        # sql = "delete from " + self.db_table
        # # print(sql)
        # n = self.cur.execute(sql)
        # # print(n)
        # self.conn.commit()

    # 插入数据
    def insertData(self, table, my_dict):
        '''
        新增数据共通方法
        table 表名  str
        my_dict 列  dict
        '''
        pass
        # # print(my_dict)
        # # print(my_dict['jj_code'])
        # cols = ', '.join(my_dict.keys())
        # # values = '","'.join(my_dict.values())
        # values = "','".join('%s' % v for v in my_dict.values())

        # sql = "replace into %s (%s) values (%s)" % (table, cols,
        #                                             "'" + values + "'")
        # print(sql)
        # self.cur.execute(sql)
        # self.conn.commit()

    # 获取execl数据
    def getExeclData(self):
        # 打开文件
        wb = xlrd.open_workbook(self.file_name)
        # 查看工作表
        sheet_names = wb.sheet_names()
        for sheet_name in sheet_names:
            sh = wb.sheet_by_name(sheet_name)
            if sheet_name == '品名分类':
                # self.doDataProduct_group(sh)
                pass
            elif sheet_name == '品名':
                # pass
                self.doDataProduct(sh)
            # elif sheet_name == '品名统计':
            #     self.doDataProduct(sh)

            # print(sheet_name)
            # sh = wb.sheet_by_name(sheet_name)

    # 品名
    def doDataProduct(self, sh):

        self.db_table = 'v_tbl_dd_product_tax_rate'
        self.deleteData()  # 删除旧数据
        self.db_table = 'v_tbl_dd_product_item'
        self.deleteData()  # 删除旧数据
        self.db_table = 'v_tbl_dd_product'
        self.deleteData()  # 删除旧数据

        sql = "select PRODUCT_GROUP_NAME,PRODUCT_GROUP_CODE from v_tbl_dd_product_group"
        r = self.cur.execute(sql)  # 返回条数
        pro_group_result = self.cur.fetchall()
        # print(pro_group_result)

        pro_group_code_dict = {}
        for group in pro_group_result:
            pro_group_code_dict[group[0]] = group[1]

        titleRow = 15
        titles = []
        for rowNum in range(sh.nrows):
            rowVale = sh.row_values(rowNum)

            if rowNum == titleRow:
                titles = rowVale
            elif rowNum > titleRow:
                data = rowVale
                # print(data)
                result = {}
                # print(titles)
                for j in range(len(titles)):
                    result[titles[j]] = data[j]
                print(result)

                my_dict = {}
                my_dict['id'] = str(uuid.uuid1())
                my_dict['USING_FLAG'] = '1'
                my_dict['RESERVED_STR2'] = '0'
                my_dict['create_date'] = self.getCurrentTime()
                my_dict['create_user'] = 'python'
                my_dict['TENANT_CODE'] = self.TENANT_CODE

                # PRODUCT_CODE = str(result['品名代码*']).split('.')[0]
                PRODUCT_CODE = result['品名代码*']
                my_dict['PRODUCT_CODE'] = PRODUCT_CODE
                my_dict['PRODUCT_NAME'] = result['品名*']
                if result['主单位*'] == '吨':
                    my_dict['MEASUREMENT_UNIT_CODE'] = 'TNE'
                if result['辅单位*'] == '件':
                    my_dict['PRODUCT_UNIT_CODE'] = 'JIAN'
                else:
                    my_dict['PRODUCT_UNIT_CODE'] = 'BALE'

                type_pro = result['品名分类*']

                if type_pro in pro_group_code_dict:
                    my_dict['PRODUCT_GROUP_CODE'] = pro_group_code_dict[
                        type_pro]

                if '籽棉' in type_pro:
                    my_dict['REPO_TYPE'] = 'SEED'
                elif '进口' in type_pro and '棉' in type_pro:
                    my_dict['REPO_TYPE'] = 'IMPORT'
                elif '内采' in type_pro:
                    my_dict['REPO_TYPE'] = 'IMPORT'
                elif '棉' == type_pro[-1:]:
                    my_dict['REPO_TYPE'] = 'DOMESTIC'
                else:
                    my_dict['REPO_TYPE'] = 'OTHERS'

                if '绒棉' in type_pro:
                    if '新疆' in type_pro or '地产' in type_pro:
                        if '细' in type_pro:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1F1'
                        elif '中' in type_pro:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1F2'
                        elif '长' in type_pro:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1F3'
                        else:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1'

                    elif '进口' in type_pro:
                        if '细' in type_pro:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2F1'
                        elif '中' in type_pro:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2F2'
                        elif '长' in type_pro:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2F3'
                        else:
                            my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2'
                elif '棉籽' in type_pro:
                    if '新疆' in type_pro or '地产' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D2E1'
                    elif '进口' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D2E2'
                    else:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D2'
                elif '短绒' in type_pro:
                    if '新疆' in type_pro or '地产' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D3E1'
                    elif '进口' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D3E2'
                    else:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D3'
                elif '化纤原料' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D5'
                elif '化纤' in type_pro:
                    if '新疆' in type_pro or '地产' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D4E1'
                    elif '进口' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D4E2'
                    else:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D4'
                elif '棉纱' in type_pro:
                    if '新疆' in type_pro or '地产' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D6E1'
                    elif '进口' in type_pro:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D6E2'
                    else:
                        my_dict['PRODUCT_COLLECT_CODE'] = 'D6'
                else:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D7'

                my_dict['ALLOCATION_TYPE'] = '0'
                # 基准价格（暂定）
                my_dict['PRICE_ITEM_CODE'] = '00000001'

                # print(my_dict)
                self.insertData(self.db_table, my_dict)

                #         v_tbl_dd_product_item
                my_dict = {}
                my_dict['id'] = str(uuid.uuid1())
                my_dict['USING_FLAG'] = '1'
                my_dict['create_date'] = self.getCurrentTime()
                my_dict['create_user'] = 'python'
                my_dict['TENANT_CODE'] = self.TENANT_CODE
                my_dict['PRODUCT_CODE'] = str(result['品名代码*']).split('.')[0]
                my_dict['PRODUCT_ITEM_CODE'] = str(
                    result['品名代码*']).split('.')[0]
                my_dict['RESERVED_STR1'] = result['品名*']
                # print(my_dict)

                self.insertData('v_tbl_dd_product_item', my_dict)

                # v_tbl_dd_product_tax_rate
                my_dict = {}
                my_dict['id'] = str(uuid.uuid1())
                my_dict['create_date'] = self.getCurrentTime()
                my_dict['create_user'] = 'python'
                my_dict['TENANT_CODE'] = self.TENANT_CODE

                my_dict['PRODUCT_CODE'] = str(result['品名代码*']).split('.')[0]
                my_dict['TAX_TYPE'] = '1'
                my_dict['BEGIN_DATE'] = '20200801'
                my_dict['TAX_RATE'] = '13'
                if '010203020111' != my_dict[
                        'PRODUCT_CODE'] and '010201030111' != my_dict[
                            'PRODUCT_CODE']:
                    # print(my_dict)
                    self.insertData('v_tbl_dd_product_tax_rate', my_dict)

    # 品名分类    v_tbl_dd_product_group
    def doDataProduct_group(self, sh):
        self.db_table = 'v_tbl_dd_product_group'

        titleRow = 6
        titles = []
        for rowNum in range(sh.nrows):
            rowVale = sh.row_values(rowNum)

            if rowNum == titleRow:
                titles = rowVale
            elif rowNum > titleRow:
                data = rowVale
                # print(data)
                result = {}
                # print(titles)
                for j in range(len(titles)):
                    result[titles[j]] = data[j]
                # print(result)

                my_dict = {}
                my_dict['id'] = str(uuid.uuid1())
                my_dict['create_date'] = self.getCurrentTime()
                my_dict['create_user'] = 'python'
                my_dict['TENANT_CODE'] = self.TENANT_CODE

                my_dict['PRODUCT_GROUP_CODE'] = result['品名分类代码*']
                my_dict['PRODUCT_GROUP_NAME'] = result['品名分类名称*']
                my_dict['PARENT_CODE'] = result['上级分类编码']

                # print(my_dict)
                self.insertData(self.db_table, my_dict)


if __name__ == "__main__":
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    starttime = datetime.datetime.now()
    print(starttime)

    mysql_class = mysql_class()  # 类初始化
    mysql_class.ljdb()  # 连接MySQL
    # mysql_class.db = 'zhongmian_qc'
    mysql_class.TENANT_CODE = 'T0021'

    mysql_class.file_name = '中棉系统基础数据初始化清单-运营监管.xlsx'
    mysql_class.getExeclData()  # 数据导入

    endtime = datetime.datetime.now()

    print(endtime)
    print('\n数据处理成功!所用时间为：' + str((endtime - starttime).seconds))
